﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using DBUtil;
using ZXCompany.Models.Entity;
namespace ZXCompany.Models.Dao
{
    public class ZX_TrainListDao
    {
        private Database db = new Database();

        private List<ZX_TrainList> GetListFromDT(DataTable dt)
        {
            List<ZX_TrainList> ltrain = new List<ZX_TrainList>();

            foreach (DataRow dr in dt.Rows)
            {
                ZX_TrainList ztrain = new ZX_TrainList();
                ztrain.addTime = dr["addTime"].ToString();
                ztrain.addUserID = Convert.ToInt32(dr["addUserID"]);
                ztrain.ID = Convert.ToInt32(dr["ID"]);
                ztrain.trainDetail = dr["trainDetail"].ToString();
                ztrain.trainImage = dr["trainImage"].ToString();
                ztrain.trainName = dr["trainName"].ToString();
                ztrain.updateTime = dr["updateTime"].ToString();
                ltrain.Add(ztrain);
            }
            return ltrain;
        }
        /// <summary>
        /// 选择培训系列
        /// </summary>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public List<ZX_TrainList> Select(SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                                  ,isnull([trainName],'') as [trainName]
                                  ,isnull([trainImage],'') as [trainImage]
                                  ,isnull([trainDetail],'') as [trainDetail]
                                  ,isnull([addUserID],0) as [addUserID]
                                  ,isnull([addTime],'') as [addTime]
                                  ,isnull([updateTime],'') as [updateTime]
                              FROM [ZX_TrainList]";
                DataTable dt = db.Squery(sql, sconn);
                return GetListFromDT(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public ZX_TrainList SelectByID(int id, SqlConnection sconn)
        {
            try
            {
                string sql = @"SELECT isnull([ID],0) as [ID]
                                  ,isnull([trainName],'') as [trainName]
                                  ,isnull([trainImage],'') as [trainImage]
                                  ,isnull([trainDetail],'') as [trainDetail]
                                  ,isnull([addUserID],0) as [addUserID]
                                  ,isnull([addTime],'') as [addTime]
                                  ,isnull([updateTime],'') as [updateTime]
                              FROM [ZX_TrainList] where [ID]=@ID";
                SqlParameter[] spvalues = new SqlParameter[] { 
                new SqlParameter("@ID",id)
                };
                DataTable dt = db.Squery(sql, sconn,spvalues);
                List<ZX_TrainList> ltrain = GetListFromDT(dt);
                return ltrain.Count > 0?ltrain[0]:new ZX_TrainList();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Delete(int typeid, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"delete FROM [ZX_TrainList] where ID=@ID";
                SqlParameter[] spvalues = new SqlParameter[]{
                    new SqlParameter("@ID",typeid)
                };
                db.Udaquery(sql, sconn, stran, spvalues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Insert(Entity.ZX_TrainList zxei, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = @"insert into ZX_TrainList 
                              (trainName,trainImage,trainDetail,addUserID,addTime) values 
                              ( @trainName
                                ,@trainImage
                                ,@trainDetail
                                ,@addUserID
                                ,@addTime)";
                SqlParameter[] spvalues = new SqlParameter[]{
                    new SqlParameter("@trainName",Database.SqlNull(zxei.trainName)),
                    new SqlParameter("@trainImage",Database.SqlNull(zxei.trainImage)),
                    new SqlParameter("@trainDetail",Database.SqlNull(zxei.trainDetail)),
                    new SqlParameter("@addUserID",Database.SqlNull(zxei.addUserID)),
                    new SqlParameter("@addTime",Database.SqlNull(zxei.addTime))
                };
                db.Udaquery(sql, sconn, stran, spvalues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据id获取信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="sconn"></param>
        /// <returns></returns>
        public void Update(Entity.ZX_TrainList zxei, SqlConnection sconn, SqlTransaction stran)
        {
            try
            {
                string sql = "update ZX_TrainList set [trainName]=@trainName";
                if (zxei.trainImage != "")
                {
                    sql += ",[trainImage]=@trainImage";
                }
                sql += @",[trainDetail]=@trainDetail
                        ,[addUserID]=@addUserID
                        ,[addTime]=@addTime
                        ,[updateTime]=@updateTime
                    FROM [ZX_TrainList] where [ID]=@ID";
                SqlParameter[] spvalues = new SqlParameter[]{
                    new SqlParameter("@ID",zxei.ID),
                    new SqlParameter("@trainName",Database.SqlNull(zxei.trainName)),
                    new SqlParameter("@trainImage",Database.SqlNull(zxei.trainImage)),
                    new SqlParameter("@trainDetail",Database.SqlNull(zxei.trainDetail)),
                    new SqlParameter("@addUserID",Database.SqlNull(zxei.addUserID)),
                    new SqlParameter("@addTime",Database.SqlNull(zxei.addTime)),
                    new SqlParameter("@updateTime",Database.SqlNull(zxei.updateTime))
                };
                db.Udaquery(sql, sconn, stran, spvalues);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}